* Merging data to compile dataset for "Democracy and Financial Crisis", IO
* Phillip Y. Lipscy 

**
** Specify local directory path: this will need to be changed to your local folder when performing replication.
** 

cd "C:\Users\plipscy.POL-39M8VF2\Main Folder\Academic Projects\Financial Crisis Democracy\ReplicationFilesforIO\LipscyReplicationFiles\SourceData\"


** Opening Reinhart Rogoff Data (compiled manually from country-by-country excel spreadsheets and modified to fit COW format
** Data source: http://www.carmenreinhart.com/data/browse-by-topic/topics/7/
use "ReinhartRogoff_crisisdata.dta", clear

** Merging Polity index (democracy score) from Correlates of War: http://www.systemicpeace.org/polityproject.html
merge 1:1 cyear using "p4v2016.dta", keepusing(polity2_new)
drop if _merge == 2
drop _merge
rename polity2_new polity2

** Merging long-term historical GDP/capita from Angus Madisson Project: https://www.rug.nl/ggdc/historicaldevelopment/maddison/releases/maddison-project-database-2018

preserve
use "MaddisonHistoricalGDP.dta" , clear
rename countrycode iso3c
kountry iso3c , from(iso3c) to(cowc)
kountry iso3c , from(iso3c) to(cown)
rename _COWN_ ccode
rename _COWC_ scode
replace ccode = 364 if iso3c == "SUN"
replace scode = "USR" if iso3c == "SUN"
sort ccode year
g cyear= ccode*10000+year
duplicates drop cyear, force
drop if missing(cyear)
save "temp1.dta", replace

restore
merge 1:1 cyear using "temp1.dta", keepusing(rgdpnapc)
drop if _merge == 2
drop _merge
rename rgdpnapc gdppercap

** Merging Boix et al dichotomous democracy measure: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/28468

preserve
use "dichotomousdemocracy.dta", clear
keep ccode year democracy 
rename * boix_*
g cyear = boix_ccode*10000+ boix_year
save "temp1.dta", replace

restore

merge m:1 cyear using "temp1.dta", keep(match master) nogen

** Turnover Variable from Archigos: A Database of Political Leaders: http://privatewww.essex.ac.uk/~ksg/archigos.html

preserve
import delimited "arch_annual.txt", clear 
gen datest= date(startdate,"YMD")
format datest %td
gen dateed= date(enddate,"YMD")
format dateed %td
gen betweensted = dateed - datest
gen year = year(datest)

* Leader's Age
gen leaderage = year - yrborn

* Years in which leader switch occurs
egen leaderid = group(obsid)
gen leaderswitch = 0
replace leaderswitch = 1 if entry != "(p_year)"

* Cumulative number of leader switches during a regime's existence (or since beginning of data).  First leader is not treated as a switch.
bysort ccode : gen turnover_cum = sum(leaderswitch) - 1

* Cumulative years of regime's existence 
bysort ccode : egen regime_start = min(year)
gen regime_length = year + 1 - regime_start

* Turnover variable
gen turnover = turnover_cum/regime_length

* Start variable in 1870 to reflect data start 
replace turnover = . if year < 1870

* Leader's Tenure in Years
bysort leaderid : egen tenure_start = min(year)
gen tenure = year + 1 - tenure_start

* Time reminaing in office 
bysort leaderid : egen tenure_end = max(year)
gen time_remaining = tenure_end - year
replace time_remaining = . if tenure_end == 2015

* Keeping only leaders with longest tenure in given year to retain only one observation per country year (for merging data)
bysort ccode year : egen year_longestleader = max(betweensted)
drop if betweensted < year_longestleader

* Fix only case of leaders in same year with identical tenure lengths (eliminates duplicates)
drop if leaderid == 1729 & year == 1929
g cyear = ccode*10000+year

save "temp1.dta", replace

restore

merge 1:1 cyear using "temp1.dta", keepusing(turnover tenure leaderage turnover_cum time_remaining regime_length regime_start) 
drop if _merge == 2
drop _merge
rename tenure sumten 
rename leaderage age

** Political Constraints by Henisz et al: https://mgmt.wharton.upenn.edu/faculty/heniszpolcon/polcondataset/

preserve
import excel "polcon2012.xls", firstrow clear 
keep cyear ccode cnts_country polity_country icrg_country ctrynm year polconiii polconv polconvj executivepartydom
rename * henisz_*
rename henisz_cyear cyear
save "temp1.dta", replace

restore
merge m:1 cyear using "temp1.dta", keep(match master) nogen

** Eichengreen and Leblang Trade Openess and Capital Controls: https://sites.google.com/site/davidaleblang/data-1

preserve
use "EichengreenLeblandecpol2008.dta", clear
kountry ifs, from(imfn) to(cowc)
kountry ifs, from(imfn) to(cown)
drop ccode
rename _COWN_ ccode
rename _COWC_ scode
sort ccode year
g cyear= ccode*10000+year
duplicates drop cyear, force
save "temp1.dta", replace

restore
merge 1:1 cyear using "temp1.dta", keepusing(tradeopen2 capcont)
drop if _merge == 2
drop _merge

** Barbieri Trade Contagion: http://www.correlatesofwar.org/data-sets/bilateral-trade

preserve
import delimited "Dyadic_COW_4.0.csv", clear 
keep ccode1 ccode2 year importer1 importer2 flow1 flow2
sort year importer1 importer2
drop if flow1 == -9 | flow2 == -9 

* Create total exports + imports 
gen imports_exports = flow1 + flow2
drop flow1 flow2

* Sum all total exports+imports by country year
bysort ccode1 year: egen sumtrade = sum(imports_exports)

* Calculate percentage of trade associated with each country
gen trade_percent = imports_exports/sumtrade

sort ccode1 year ccode2
save "temp1.dta", replace

* Merge with banking crisis variable
use "ReinhartRogoff_crisisdata.dta", clear
keep ccode country year bankingcrises
drop if missing(bankingcrises)
rename ccode ccode2
sort ccode2 year
merge 1:m ccode2 year using "temp1.dta", keep(matched master) nogen

drop country 
drop if missing(trade_percent)
order year ccode1 importer1 ccode2 importer2
sort ccode1 year ccode2

* Calculate weighted average occurance of banking crises in country's trading partners 
gen crisis_trade = trade_percent * bankingcrises
collapse (sum) crisis_trade, by(ccode1 importer1 year)

rename (crisis_trade ccode1) (t_weight_barbieri ccode)
save "temp1.dta", replace

restore

merge 1:m ccode year using "temp1.dta", keep(matched master) nogen

drop importer1


** Barbieri and World Bank Trade/GDP to supplement recent years for historical data from Eichengreen & Leblang 

preserve
use "BarbieriTrade.dta", clear
merge m:1 ccode year using "wbgdpdata_complete.dta", keepusing(gdp) keep(matched master) nogen
g tradegdp = totaltrade*1000000/ gdp
g cyear= ccode*10000+year
save "temp1.dta", replace

restore

merge 1:1 cyear using "temp1.dta", keepusing(imports exports totaltrade tradegdp)
drop if _merge == 2
drop _merge
replace tradeopen2 = tradegdp if missing(tradeopen2)


** Liberalization from Detragiache, Abiad, and Tressel: https://www.imf.org/en/Publications/WP/Issues/2016/12/31/A-New-Database-of-Financial-Reforms-22485

preserve
use "AbiadFinancialReformDataset.dta", clear
kountry ifs, from(imfn) to(cowc)
kountry ifs, from(imfn) to(cown)
rename _COWN_ ccode
rename _COWC_ scode
sort ccode year
g cyear= ccode*10000+year
duplicates drop cyear, force

* Omitting banking supervision from financial reform index to obtain liberalization index
g finliberal = finreform - bankingsuperv
save "temp1.dta", replace

restore

merge 1:1 cyear using "temp1.dta", keepusing(finliberal intlcapital)
drop if _merge == 2
drop _merge

** Schularick Taylor Credit Data: http://www.macrohistory.net/data/

preserve
use "JSTdatasetR2.dta", clear
kountry ifs, from(imfn) to(cowc)
kountry ifs, from(imfn) to(cown)
rename _COWN_ ccode
rename _COWC_ scode
sort ccode year
duplicates drop ccode year, force
rename gdp gdp_JST
g cyear= ccode*10000+year
save "temp1.dta", replace

restore

merge 1:1 cyear using "temp1.dta", keepusing(stir ltrate tloans gdp_JST cpi)
drop if _merge == 2
drop _merge

** Beck et al Credit data: http://www.worldbank.org/en/publication/gfdr/data/financial-structure-database

preserve
import delimited "Beck_etal_FinancialStructureandDevelopmentDataset.csv", clear
kountry cncode, from(iso3c) to(cowc)
kountry cncode, from(iso3c) to(cown)
rename _COWN_ ccode
rename _COWC_ scode
sort ccode year
g cyear= ccode*10000+year
drop if missing(cyear)
save "temp1.dta", replace

restore

merge 1:1 cyear using "temp1.dta", keepusing(pcrdbofgdp)
drop if _merge == 2
drop _merge




**
** Setting up variables for analysis
**


** Generating Democracy Stock variable based on Gerring et al 2005. Discount rate is 5% for Table 3; see analysis file for other discount rates

g stockdemocracy = .
g incstockdem = .
g cumstockdem = .

foreach i of num 1800/2009 {
	bysort ccode (year): replace incstockdem = (polity2*0.95^(-(year-`i')))
	bysort ccode: replace cumstockdem = sum(incstockdem)
	bysort ccode: replace stockdemocracy = cumstockdem if year ==`i'
}

** Constraints Stock for Table A6 

g constraintsstock = .
g incstockconst = .
g cumstockconst = .

foreach i of num 1800/2009 {
	bysort ccode (year): replace incstockconst = (henisz_polconiii*0.99^(-(year-`i')))
	bysort ccode: replace cumstockconst = sum(incstockconst)
	bysort ccode: replace constraintsstock = cumstockconst if year ==`i'
}

bysort ccode: g l5constraintsstock = constraintsstock[_n-5]

** Log of leader tenure
g lnsumten = ln(sumten)

** Political party turnover 
replace henisz_executivepartydom = "" if henisz_executivepartydom=="na" | henisz_executivepartydom=="?" | missing(regime_length)
bysort ccode (year): g partychange = 0 if henisz_executivepartydom == henisz_executivepartydom[_n-1] & !missing(henisz_executivepartydom)
bysort ccode (year): replace partychange = 1 if henisz_executivepartydom != henisz_executivepartydom[_n-1]
bysort ccode (year): g partycum = sum(partychange) if !missing(partychange)
bysort ccode (year): g partyturnover = partycum/regime_length

** Decade Dummies for systemic crisis years 

foreach i in 1890 1900 1910 1920 1930 1990 2000 {
	gen dum`i' = 0
	replace dum`i' = 1 if year >= `i' & year < `i'+10
}


** Average polity score of all countries 

preserve
collapse (mean) polity2, by(year)
rename polity2 ave_polity2
save "temp1.dta", replace
restore
merge m:1 year using "temp1.dta", nogen

** Combining private credit variables

g creditgdp = tloans/ gdp_JST
g newprivatecredit = pcrdbofgdp
replace newprivatecredit = creditgdp*100 if missing(pcrdbofgdp)

** Contagion measure scaled by log trade openness
g scaled_barbieri = t_weight_b * tradeopen2
g log_scaled_barbieri = ln(scaled_barbieri+1)


** Differenced and Lagged Variables

bysort ccode (year): g dpolity2 = polity2 - polity2[_n-1]
bysort ccode (year): g d5polity2 = polity2 - polity2[_n-5]
bysort ccode (year): g d10polity2 = polity2 - polity2[_n-10]
bysort ccode (year): g d20polity2 = polity2 - polity2[_n-20]

bysort ccode (year): g lpolity1 = polity2[_n-1]
bysort ccode (year): g lpolity5 = polity2[_n-5]
bysort ccode (year): g lpolity10 = polity2[_n-10]
bysort ccode (year): g lpolity20 = polity2[_n-20]
bysort ccode (year): g lfinlib5 = finlib[_n-5]
bysort ccode (year): g lfinlib10 = finlib[_n-10]


** Alternative operationalizations of democracy for Table 3
g newdemocracy5 = 0
replace newdemocracy5 = 1 if polity2 > 6 & lpolity10 < 7
g undstabledemocracydicho = 0
replace undstabledemocracydicho = 1 if d10polity2 != 0
g consdem = 0 if !missing(polity2)
replace consdem = 1 if polity2 > 6
g clearauto = 0 if !missing(polity2)
replace clearauto = 1 if polity2 < -6
g transdem = 0 if !missing(polity2)
replace transdem = 1 if consdem == 0 & clearauto == 0

** For easier interpretation of GDP
g gdppercap1000 = gdppercap/1000


** Including Norway and Singapore during union periods in boix_democracy measure for full consistency of covered countries with polity2 
replace boix_democracy = 0 if ccode == 385 & year >= 1814 & year <= 1897
replace boix_democracy = 1 if ccode == 385 & (year == 1898 | year == 1899)
replace boix_democracy = 0 if ccode == 830 & year >=1959 & year <=1962


** Create banking crisis variable for analysis for which only first year of crisis is coded 1 
g bcrises = 0 if bankingcrises == 1 | bankingcrises ==0
bysort ccode: replace bcrises = 1 if bankingcrises[_n] - bankingcrises[_n-1] == 1
* Fix India, which enters the data in a banking crisis
replace bcrises = 1 if ccode == 750 & year == 1947

** Drop observations from bcrises that are not at risk due to ongoing crises
replace bcrises = . if (bankingcrises[_n-1] == 1) & bankingcrises == 1



** Saving file for analysis

save "financialcrisisdemocracy.dta", replace
